import pymysql
import numpy as np
import matplotlib.pyplot as plt

con = pymysql.connect(host='localhost',user='root',password='micheliu',db='world')

cur = con.cursor()
cur.execute("SELECT subject FROM studentscore");
re = cur.fetchall()

cur.execute("SELECT MAX(china) as '语文', MAX(math) as '数学' FROM (SELECT "
            "case subject WHEN '语文' THEN score ELSE null END as 'china',"
            "case subject WHEN '数学' THEN score ELSE null END as 'math' from studentscore) tmp;")

res = cur.fetchall()
cur.close()
con.close()

xArray = [list(row) for row in re]
print(xArray)
x = np.array(xArray).flatten().tolist()
print("X轴的数据为:", x)

yArray = [list(row) for row in res]
print(yArray)
y = np.array(yArray).flatten().tolist()
print("Y轴的数据为:", y)

plt.rcParams['font.sans-serif'] = [u'SimHei']

bar_wight = 0.3

x_value = np.arange(len(x))
plt.bar(x_value, y, tick_label=x, width=bar_wight, label='学生')

plt.xticks(x_value + bar_wight / 2, x)

plt.axhline(y=(85.5 + 91.0)/ 2, linestyle='--',label='平均成绩')

plt.ylabel("平均成绩(分)")
plt.title("学生平均成绩分析图")
plt.legend()
plt.show()